/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.edu.fatecmm.cadastrotimes.banco;
import br.edu.fatecmm.cadastrotimes.beans.BnTimes;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author raphael
 */
public class TimesDAO {
     private Connection conn;
    
    public TimesDAO() throws Exception{
        
        try{
            this.conn = ConnectionFactory.getConnection();
        }
        catch(Exception e){
            throw new Exception (e.getMessage());
        }
    }
    
    public void salvar(BnTimes bnTimes) throws Exception{
        PreparedStatement ps = null;
        Connection conn = null;
        if(bnTimes != null)
            throw new Exception("O valor passado não pode ser nulo");
        
        try{
            String SQL = "INSERT INTO times(id_time, ds_time, cd_tecnico"
                    + "cd_posicao, cd_pais, cd_liga, cd_jogador ) "
                    + "VALUES (?,?,?,?,?,?)";
            conn = this.conn;
            ps = conn.prepareStatement(SQL);
            ps.setInt(1, bnTimes.getId_times());
            ps.setString(2, bnTimes.getDs_times());
            ps.setInt(3, bnTimes.getCd_tecnico());
            ps.setInt(4, bnTimes.getCd_posicao());
            ps.setInt(5, bnTimes.getCd_pais());
            ps.setInt(6, bnTimes.getCd_liga());
            ps.setInt(7, bnTimes.getCd_jogador());
            ps.executeUpdate();
        }catch(SQLException sqle)
        {
            throw new Exception("Erro ao inserir dados: " + sqle);
        }
        finally
        {
            ConnectionFactory.closeConnection(conn, ps);
        }
    }
        public void atualizar(BnTimes bnTimes) throws Exception{
        
        PreparedStatement ps = null;
        Connection conn = null;

        if(bnTimes == null)
            throw new Exception("O valor passado não pode ser nulo");

        try
        {
            String SQL = "UPDATE times SET id_time = ?, ds_time = ?, "
                    + "cd_tecnico = ?, cd_posicao = ?, cd_pais = ?, "
                    + "cd_liga = ?, cd_jogador = ? WHERE id_times = ?";
            conn = this.conn;
            ps = conn.prepareStatement(SQL);
            ps.setInt(1, bnTimes.getId_times());
            ps.setString(2, bnTimes.getDs_times());
            ps.setInt(3, bnTimes.getCd_tecnico());
            ps.setInt(4, bnTimes.getCd_posicao());
            ps.setInt(5, bnTimes.getCd_pais());
            ps.setInt(6, bnTimes.getCd_liga());
            ps.setInt(7, bnTimes.getCd_jogador());

            ps.executeUpdate();
        }
        catch(SQLException sqle)
        {
            throw new Exception("Erro ao atualizar dados: " + sqle);
        }
        finally
        {
            ConnectionFactory.closeConnection(conn, ps);
        }
        
    }
        public List todos() throws Exception
    {
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;

        try
        {
            conn = this.conn;
            ps = conn.prepareStatement("SELECT * FROM times ORDER BY ds_time");
            rs = ps.executeQuery();
            List<BnTimes> lista = new ArrayList<BnTimes>();
            while(rs.next())
            {
                int id_time = rs.getInt("id_time");
                String ds_time = rs.getString("ds_time");
                int cd_tecnico = rs.getInt("cd_tecnico");
                int cd_posicao = rs.getInt("cd_posicao");
                int cd_pais = rs.getInt("cd_pais");
                int cd_ligas = rs.getInt("cd_liga");
                int cd_jogador = rs.getInt("cd_jogador");
                
                
                BnTimes bnTimes = new BnTimes(id_time,ds_time,cd_tecnico,
                        cd_posicao,cd_pais,cd_ligas,cd_jogador);

                lista.add(bnTimes);
            }
            return lista;
        }
        catch(SQLException sqle)
        {
            throw new Exception(sqle);
        }
        finally
        {
            ConnectionFactory.closeConnection(conn, ps, rs);
        }
    }
         public BnTimes procurar(int id_time) throws Exception
    {
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;

        try
        {
            conn = this.conn;
            ps = conn.prepareStatement("SELECT * FROM times WHERE id_time = ?");
            ps.setInt(1, id_time);
            rs = ps.executeQuery();
            if(!rs.next())
            {
                throw new Exception("Não foi encontrado nenhum "
                        + "registro com o ID: " + id_time);
            }

                String ds_time = rs.getString("ds_time");
                int cd_tecnico = rs.getInt("cd_tecnico");
                int cd_posicao = rs.getInt("cd_posicao");
                int cd_pais = rs.getInt("cd_pais");
                int cd_ligas = rs.getInt("cd_liga");
                int cd_jogador = rs.getInt("cd_jogador");

            
            
             BnTimes bnTimes = new BnTimes(id_time,ds_time,cd_tecnico,
                        cd_posicao,cd_pais,cd_ligas,cd_jogador);
            
            return bnTimes;
        }
        catch(SQLException sqle)
        {
            throw new Exception(sqle);
        }
        finally
        {
            ConnectionFactory.closeConnection(conn, ps, rs);
        }
    }
         public void excluir(BnTimes bnTimes) throws Exception
    {
        PreparedStatement ps = null;
        Connection conn = null;

        if(bnTimes == null)
        {
            throw new Exception("O valor passado não pode ser nulo");
        }

        try
        {
            conn = this.conn;
            ps = conn.prepareStatement("DELETE FROM times WHERE id_time = ?");
            ps.setInt(1, bnTimes.getId_times());
            ps.executeUpdate();
        }
        catch(SQLException sqle)
        {
            throw new Exception("Erro ao excluir dados: " + sqle);
        }
        finally
        {
            ConnectionFactory.closeConnection(conn, ps);
        }
    }
    
}
